# -*- coding: utf-8 -*-
from wt.connector.db_manager.db_untils_tools import DBUntilsTools
from wt.connector.data_connect import DataConnect


class DBTidbManager(DataConnect, DBUntilsTools):

    def __init__(self, ):
        super().__init__()

    def create_ddl(self):
        """
        定义mysql插入字段类型
        :return:
        """
        col = {
            "id": "int(11) NOT NULL AUTO_INCREMENT,",
            "c_02_integer_col": "int(10) DEFAULT NULL,",
            "c_03_double_col": "double DEFAULT NULL,",
            "c_04_float_col": "float DEFAULT NULL,",
            "c_05_smallint_col": "smallint(5) DEFAULT NULL,",
            "c_06_bigint_col": "bigint(20) DEFAULT NULL,",
            "c_07_tinyint_col": "tinyint(3) DEFAULT NULL,",
            "c_08_decimal_col": "decimal(10,2) DEFAULT NULL,",
            "c_09_varchar_col": "varchar(50) DEFAULT NULL,",
            "c_10_date_col": "date DEFAULT NULL,",
            "c_11_datetime_col": "datetime DEFAULT NULL,",
            "": "PRIMARY KEY (`id`) \r\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;"
        }
        return col

    def generate_create_ddl(self, db, table):
        """
        拼接mysql ddl语句
        :param table:
        :return:
        """
        col = self.create_ddl()
        ddl_sql = 'create table {} ( '.format(table)
        for key in col:
            line = key + " " + col[key] + "\r\n"
            ddl_sql = ddl_sql + line
        return ddl_sql

    def general_col_value(self, table):
        """
        生成 mysql dml
        :param table:
        :return:
        """
        c_02_integer_val = self.get_random(-2147483648, 2147483648)
        c_03_double_val = self.get_random_float(-500, 500)
        c_04_float_val = self.get_random_float(-500, 500)
        c_05_smallint_val = self.get_random(-32768, 32767)
        c_06_bigint_val = self.get_random(-9223372036854775808, 9223372036854775807)
        c_07_tinyint_val = self.get_random(-128, 123)
        c_08_decimal_val = self.get_random_float(-500, 500)
        c_09_varchar_val = self.get_random_string(8)
        c_10_date_val = self.get_random_date()
        c_11_datetime_val = self.get_random_datetime()
        sql = "insert into {} (c_02_integer_col, c_03_double_col, c_04_float_col, c_05_smallint_col, c_06_bigint_col," \
              " c_07_tinyint_col, c_08_decimal_col, c_09_varchar_col, c_10_date_col, c_11_datetime_col) " \
              "values ({},{},{},{},{},{},{},{},{},{});" \
              "".format(table, c_02_integer_val, c_03_double_val, c_04_float_val, c_05_smallint_val, c_06_bigint_val,
                        c_07_tinyint_val, c_08_decimal_val, c_09_varchar_val, c_10_date_val, c_11_datetime_val)
        return sql

    def generate_dml_sql(self, table, lines):
        """
        批量生成 mysql dml 语句
        :param table:
        :param lines:
        :return:
        """
        insert_sql = ""
        while lines > 0:
            tmp = self.general_col_value(table)
            insert_sql = insert_sql + tmp + "\r\n"
            lines = lines - 1
        return insert_sql

    def execute_create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param is_recreate:
        :param db:
        :param table:
        :param num:
        :return:
        """
        if is_recreate:
            self.generate_create_ddl(db, table)
        insert_sql = self.generate_dml_sql(table, num)
        for line in insert_sql.strip().split("\r\n"):
            self.exec_tidb_sql(db, line)

    def exec_customer_sql(self, db, sql):
        res = self.exec_tidb_sql(db, sql)
        return res


    def show_table_sql(self, db, table):
        query_sql = "show create table {}.{}".format(db, table)
        res = self.exec_tidb_sql(db, query_sql)

    def set_primary_combkey(self, db, table):
        self.exec_tidb_sql(db, "alter table {}.{} change id id int".format(db, table))
        self.exec_tidb_sql(db, "alter table {}.{} drop primary key".format(db, table))
        add_combkey_sql = "ALTER TABLE {}.{} ADD CONSTRAINT primary_key PRIMARY KEY (id,c_02_integer_col)".format(db,
                                                                                                                  table)
        self.exec_tidb_sql(db, add_combkey_sql)

    def create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param is_recreate:
        :param db:
        :param table:
        :param num:
        :return:
        """
        # 删除表/新增表
        if is_recreate:
            ddl_sql = self.generate_create_ddl(db, table)
            self.exec_tidb_sql(db, "drop table {}".format(table))
            self.exec_tidb_sql(db, ddl_sql)
        insert_sql = self.generate_dml_sql(table, num)
        for line in insert_sql.strip().split("\r\n"):
            self.exec_tidb_sql(db, line)

    def get_count(self, db, table):
        sql = "select count(1) as cot from {}.{}".format(db, table)
        res = self.exec_tidb_sql(db, sql)
        if res['message'] == "Success":
            res_num = res['data'][0]["cot"]
            return res_num
        else:
            return None

    def execute_drop_table(self, db, table):
        sql = "drop table {}.{}".format(db, table)
        res = self.exec_tidb_sql(db, sql)
        return self.set_return_massage(res)

    def get_db_data(self, db, table):
        sql = "select * from {}.{} order by id desc limit 10".format(db, table)
        res = self.exec_tidb_sql(db, sql)
        return self.set_return_massage(res, only_data="yes")


if __name__ == '__main__':
    pass
